﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using Dapper;using System.Linq;
using Gx.Model.Entity;//实体
using Gx.Common;//通用类
using System.Linq;

namespace Gx.Dal.Manage
{
    /// <summary>
    /// 部门数据层
    /// </summary>
    public class DepartmentInfoDal : SqlServerHelper, IDisposable
    {
        public bool AddDepartmentInfo(E_DepartmentInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO E_DepartmentInfo");
            strSql.Append("(");
            strSql.Append("DepName,");
            strSql.Append("ParentId,");
            strSql.Append("Status");
            strSql.Append(")");
            strSql.Append("VALUES");
            strSql.Append("(");
            strSql.Append("@DepName,");
            strSql.Append("@ParentId,");
            strSql.Append("@Status");
            strSql.Append(")");
            List<SqlParameter> pList = new List<SqlParameter>();
            pList.Add(new SqlParameter("@DepName", SqlDbType.NVarChar, 60) { Value = model.DepName });
            pList.Add(new SqlParameter("@ParentId", SqlDbType.Int, 4) { Value = model.ParentId });
            pList.Add(new SqlParameter("@Status", SqlDbType.TinyInt, 1) { Value = model.Status });
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                return conn.Execute(strSql.ToString(), model) > 0;
            }
        }

        public bool DelDepartmentInfo(int Id)
        {
            string strSql = "UPDATE E_DepartmentInfo SET Status=1 WHERE Id=@Id";
 
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                return conn.Execute(strSql.ToString(), new { Id}) > 0;
            }
        }

        public bool EditDepartmentInfo(E_DepartmentInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE E_DepartmentInfo SET ");
            strSql.Append(" DepName=@DepName,");
            strSql.Append(" ParentId=@ParentId,");
            strSql.Append(" Status=@Status");
            strSql.Append(" WHERE Id=@Id");
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                return conn.Execute(strSql.ToString(), model) > 0;
            }
        }

        public List<E_DepartmentInfo> GetDepartmentInfoList(string strWhere, int pageIndex, int pageSize, out int totalCount)
        {
            totalCount = GetDepartmentInfoCount(strWhere);
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM (");
            strSql.Append("SELECT ROW_NUMBER() OVER(ORDER BY Id DESC) AS ORDERBYID");
            strSql.Append(",Id");
            strSql.Append(",DepName");
            strSql.Append(",ParentId");
            strSql.Append(",Status");
            strSql.AppendFormat(" FROM E_DepartmentInfo WITH(NOLOCK) WHERE 1=1 {0}) AS T WHERE T.ORDERBYID  BETWEEN {1} AND {2}", strWhere, pageIndex, pageSize);
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                List<E_DepartmentInfo> list = conn.Query<E_DepartmentInfo>(strSql.ToString()).ToList();
                return list;
            }
        }

        public List<E_DepartmentInfo> GetDepartmentInfoList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT");
            strSql.Append(" Id");
            strSql.Append(",DepName");
            strSql.Append(",ParentId");
            strSql.Append(",Status");
            strSql.AppendFormat(" FROM E_DepartmentInfo WITH(NOLOCK) WHERE Status=0 {0}", strWhere);
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                List<E_DepartmentInfo> list = conn.Query<E_DepartmentInfo>(strSql.ToString()).ToList();
                return list;
            }
        }

        /// <summary>
        /// 根据Id剔除我下面所有的子级
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public List<E_DepartmentInfo> GetDepartmentInfoList(int Id)
        {
 
            string strSql = @"WITH    T AS ( SELECT   Id
               FROM     E_DepartmentInfo WITH ( NOLOCK )
               WHERE    Id = @Id
                        AND [Status] = 0
               UNION ALL
               SELECT   A.Id
               FROM     E_DepartmentInfo AS A WITH ( NOLOCK )
                        JOIN T AS B ON A.ParentId = B.Id
             )
    SELECT  Id ,
            DepName ,
            ParentId ,
            Status
    FROM    dbo.E_DepartmentInfo WITH ( NOLOCK )
    WHERE   [Status] = 0
            AND Id NOT IN ( SELECT  Id
                            FROM    T )";
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                List<E_DepartmentInfo> list = conn.Query<E_DepartmentInfo>(strSql.ToString(),new { Id }).ToList();
                return list;
            }
        }


        public int GetDepartmentInfoCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat("SELECT COUNT(*) FROM E_DepartmentInfo WITH(NOLOCK) WHERE 1=1 {0}", strWhere);
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                object obj = conn.ExecuteScalar(strSql.ToString());
                return obj.IntParse();
            }
        }

        public E_DepartmentInfo GetE_DepartmentInfoModel(int Id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT ");
            strSql.Append("Id");
            strSql.Append(",DepName");
            strSql.Append(",ParentId");
            strSql.Append(",Status");
            strSql.Append(" FROM E_DepartmentInfo WITH(NOLOCK) WHERE Id=@Id");
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                E_DepartmentInfo model = conn.Query<E_DepartmentInfo>(strSql.ToString(), new { Id }).SingleOrDefault();
                return model;
            }
        }
    }
}
